<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Derby tools</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="Apache Derby, Java, tutorial, database">
<meta name="description" content="In this part of the Apache Derby tutorial,
we will cover the Derby tools.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>
</head>

<body>

<div class="container">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>


<div class="content">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Derby tools</h1>

<p>
In this chapter, we will mention Derby tools. The Derby tools and utilities 
are a set of scripts supplied with Derby. They are typically used to create, 
inspect, and update a Derby database.
</p>

<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* NewSquare */
google_ad_slot = "0364418177";
google_ad_width = 300;
google_ad_height = 250;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 


<p>
In this page, we will mention the sysinfo, the dblook, the ij, the startNetworkServer
and the stopNetworkServer tools. Note that these tools have .bat extension
on Windows. 
</p>

<h2>Launching Derby tools</h2>

<p>
Derby tools can be run in two ways. We use the script names located in the
bin directory of the Derby installation directory. Or we can use the
derbyrun.jar file to launch them.
</p>

<pre>
$ ij
$ java -jar $DERBY_HOME/lib/derbyrun.jar ij
</pre>

<p>
Assuming that the Derby bin directory is added to the PATH environment variable,
we can launch the ij tool by specifying the name of the script in the terminal. 
The second line runs the ij using the derbyrun.jar file. 
</p>

<h2>sysinfo</h2>

<p>
The <b>sysinfo</b> tool provides information about the Operating system, Java
and Derby. It will print among others Java version, Java home directory,
OS version, Java runtime version, Derby version, current and supported locales.
The tool can be useful to track down some installation or configuration issues 
with Derby.
</p>

<pre>
$ sysinfo
------------------ Java Information ------------------
Java Version:    1.6.0_30
Java Vendor:     Sun Microsystems Inc.
Java home:       /home/janbodnar/bin/jdk1.6.0_30/jre
Java classpath:  /home/janbodnar/bin/derby/lib/derby.jar:/home/janbodnar/bin/derby/lib/
    derbynet.jar:/home/janbodnar/bin/derby/lib/derbytools.jar:/home/janbodnar/bin/
    derby/lib/derbyclient.jar
OS name:         Linux
OS architecture: i386
OS version:      3.0.0-15-generic
Java user name:  janbodnar
Java user home:  /home/janbodnar
...
</pre>

<p>
This is an excerpt from the information provided on a particular system.
</p>


<h2>ij</h2>

<p>
The <b>ij</b> is an interactive scripting tool. It is used for running scripts or 
interactive queries against a Derby database.
</p>

<pre>
$ cat cars.sql
CREATE SCHEMA USER12;
CREATE TABLE CARS(ID INT PRIMARY KEY, NAME VARCHAR(30), PRICE INT);
INSERT INTO CARS VALUES(1, 'Audi', 52642);
INSERT INTO CARS VALUES(2, 'Mercedes', 57127);
INSERT INTO CARS VALUES(3, 'Skoda', 9000);
INSERT INTO CARS VALUES(4, 'Volvo', 29000);
INSERT INTO CARS VALUES(5, 'Bentley', 350000);
INSERT INTO CARS VALUES(6, 'Citroen', 21000);
INSERT INTO CARS VALUES(7, 'Hummer', 41400);
INSERT INTO CARS VALUES(8, 'Volkswagen', 21600);
</pre>

<p>
We have a cars.sql file which creates a database schema and a CARS table. 
</p>

<pre>
$ ij
ij version 10.8
ij> connect 'jdbc:derby:dbs/testdb;user=user12;create=true';
</pre>

<p>
We start the ij tool. If we had not added the Derby's bin directory
to the PATH variable, we would have to specify the whole path to the
ij tool. We create a testdb database in the dbs directory and make 
a connection to it. The dbs directory is a subdirectory of our current 
directory, where the ij was launched.
</p>

<pre>
ij> SHOW CONNECTIONS;
CONNECTION0* -  jdbc:derby:dbs/testdb
* = current connection
</pre>

<p>
The <code>SHOW CONNECTIONS</code> statement displays opened connections
to Derby databases. 
</p>

<pre>
ij> RUN 'cars.sql';
ij> CREATE SCHEMA USER12;
0 rows inserted/updated/deleted
ij> CREATE TABLE CARS(ID INT PRIMARY KEY, NAME VARCHAR(30), PRICE INT);
0 rows inserted/updated/deleted
ij> INSERT INTO CARS VALUES(1, 'Audi', 52642);
1 row inserted/updated/deleted
ij> INSERT INTO CARS VALUES(2, 'Mercedes', 57127);
1 row inserted/updated/deleted
ij> INSERT INTO CARS VALUES(3, 'Skoda', 9000);
...
</pre>

<p>
We load and execute the cars.sql site. We are informed about the 
ongoing operations.
</p>

<pre>
ij> SELECT * FROM CARS;
ID         |NAME                          |PRICE      
------------------------------------------------------
1          |Audi                          |52642      
2          |Mercedes                      |57127      
3          |Skoda                         |9000       
4          |Volvo                         |29000      
5          |Bentley                       |350000     
6          |Citroen                       |21000      
7          |Hummer                        |41400      
8          |Volkswagen                    |21600      

8 rows selected
</pre>

<p>
We select all rows from the CARS table. 
</p>

<pre>
ij> connect 'jdbc:derby:dbs/testdb;shutdown=true';
ERROR 08006: Database 'dbs/testdb' shutdown.
</pre>

<p>
Shutting down a database in Derby results in an exception. 
The ERROR 08006 is expected. 
</p>

<pre>
ij> SHOW CONNECTIONS;
No current connection
</pre>

<p>
The connection is closed.
</p>

<pre>
ij> EXIT;
</pre>

<p>
We quit the ij tool with the EXIT command. Note that each command is 
followed by semicolon. 
</p>


<h2>dblook</h2>

<p>
The <b>dblook</b> tool is used to save the data definition language of 
database objects including tables, views, indexes or triggers. 
</p>


<pre>
$ dblook -d jdbc:derby:dbs/testdb
-- Timestamp: 2012-02-12 13:33:53.677
-- Source database is: dbs/testdb
-- Connection URL is: jdbc:derby:dbs/testdb
-- appendLogs: false

-- ----------------------------------------------
-- DDL Statements for schemas
-- ----------------------------------------------

CREATE SCHEMA "USER12";

-- ----------------------------------------------
-- DDL Statements for tables
-- ----------------------------------------------

CREATE TABLE "USER12"."CARS" ("ID" INTEGER NOT NULL, 
    "NAME" VARCHAR(30), "PRICE" INTEGER);

-- ----------------------------------------------
-- DDL Statements for keys
-- ----------------------------------------------

-- primary/unique
ALTER TABLE "USER12"."CARS" ADD CONSTRAINT "SQL120212131535700" PRIMARY KEY ("ID");
</pre>

<p>
In the above example, we have dumped the objects from the testdb database.
With the -d option we have provided the connection URL to the database. 
In our case the dblook tool saved a database schema and one table. 
With the -o option the output can be redirected to a file. 
</p>


<h2>startNetworkServer &amp; stopNetworkServer</h2>

<p>
The two scripts start and stop the Derby Network server. In case of a networked
server, multiple connections to a Derby database may be created. 
</p>

<pre>
$ startNetworkServer &amp;
[1] 3742
$ Sun Feb 12 14:22:30 CET 2012 : Security manager installed using 
the Basic server security policy.
Sun Feb 12 14:22:30 CET 2012 : Apache Derby Network Server - 10.8.2.2 - 
(1181258) started and ready to accept connections on port 1527
</pre>

<p>
Here we start the Derby Network Server with the startNetworkServer script.
</p>

<pre>
ij> connect 'jdbc:derby://localhost:1527/dbs/testdb';
</pre>

<p>
Here we connect to the testdb database via the Derby Network Server. 
The connection URL is different for networked connections. 
</p>

<pre>
ij> SELECT * FROM USER12.CARS;
ID         |NAME                          |PRICE      
------------------------------------------------------
1          |Audi                          |52642      
2          |Mercedes                      |57127      
3          |Skoda                         |9000       
4          |Volvo                         |29000      
5          |Bentley                       |350000     
6          |Citroen                       |21000      
7          |Hummer                        |41400      
8          |Volkswagen                    |21600      

8 rows selected
</pre>

<p>
We select all cars from the CARS table. Since we have not provided the
database schema in the connection URL, we must specify it now. The database
schema is the user name; in our case USER12. 
</p>

<pre>
$ stopNetworkServer
Sun Feb 12 14:48:51 CET 2012 : Apache Derby Network Server 
- 10.8.2.2 - (1181258) shutdown
$ Sun Feb 12 14:48:51 CET 2012 : Apache Derby Network Server 
- 10.8.2.2 - (1181258) shutdown
</pre>

<p>
We have stopped the server with the stopNetworkServer script. 
</p>

<p>
In the chapter, we have written about Derby tools.
</p>

<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified March 3, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

